﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WebStudio.Business.Entity;
using WebStudio.Framework.Helper;
using System.Data;

namespace WebStudio.Business.Rule
{
    public class ArticalRule
    {
        #region 公有方法
        /// <summary>
        /// 获取信息列表
        /// </summary>
        /// <returns></returns>
        public static List<ArticalEntity> GetArticalList(int cid)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            using (var dr = DBHelper.ExecuteReader(string.Format("SELECT * FROM ArticalTB WHERE CategoryId={0}", cid)))
            {
                return FillDate(dr);
            }
        }
        /// <summary>
        /// 获取信息列表
        /// </summary>
        /// <returns></returns>
        public static List<ArticalEntity> GetArticalList(string condition)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            using (var dr = DBHelper.ExecuteReader(string.Format("SELECT * FROM ArticalTB {0}", condition)))
            {
                return FillDate(dr);
            }
        }
        /// <summary>
        /// 获取信息列表(有分页)
        /// </summary>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">页数</param>
        /// <param name="status">状态</param>
        /// <param name="type">类型</param>
        /// <returns></returns>
        public static List<ArticalEntity> GetArticalList(int pageIndex, int pageSize,int pageCount,int recordCount, int status, int categoryId, int pCategoryId)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            string strSql = string.Empty;
            if (pageIndex >= pageCount)
            {
                if (recordCount == 0)
                {
                    strSql = "select * from (select top " + pageSize + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
                }
                else
                {
                    strSql = "select * from (select top " + (recordCount - (pageIndex - 1) * pageSize) + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
                }
            }
            else
            {
                strSql = "select * from (select top " + pageSize + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
            }
            if (categoryId.Equals(0) && pCategoryId.Equals(0))
            {
                strSql += " and a.Status=" + status + " order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            if (!pCategoryId.Equals(0) && categoryId.Equals(0))
            {
                strSql += " and a.Status=" + status + " and a.CategoryId in (select CategoryId from CategoryTB where ParentId = " + pCategoryId + " ) order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            if (!categoryId.Equals(0))
            {
                strSql += " and a.CategoryId=" + categoryId + " and a.Status=" + status + " order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            using (var dr = DBHelper.ExecuteReader(string.Format(strSql)))
            {
                return DeepFillData(dr);
            }
        }

        /// <summary>
        /// 获取信息列表(有分页)
        /// </summary>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">页数</param>
        /// <param name="status">状态</param>
        /// <param name="type">类型</param>
        /// <returns></returns>
        public static List<ArticalEntity> GetArticalList(int pageIndex, int pageSize, int pageCount, int recordCount, int status, int categoryId, int pCategoryId,string innerCode)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            string strSql = string.Empty;
            if (pageIndex >= pageCount)
            {
                if (recordCount == 0)
                {
                    strSql = "select * from (select top " + pageSize + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
                }
                else
                {
                    strSql = "select * from (select top " + (recordCount - (pageIndex - 1) * pageSize) + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
                }
            }
            else
            {
                strSql = "select * from (select top " + pageSize + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
            }
            if (categoryId.Equals(0) && pCategoryId.Equals(0))
            {
                strSql += " and a.Status=" + status + " and a.InnerCode='" + innerCode + "' order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            if (!pCategoryId.Equals(0) && categoryId.Equals(0))
            {
                strSql += " and a.Status=" + status + " and a.CategoryId in (select CategoryId from CategoryTB where ParentId = " + pCategoryId + " ) order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            if (!categoryId.Equals(0))
            {
                strSql += " and a.CategoryId=" + categoryId + " and a.Status=" + status + " order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            using (var dr = DBHelper.ExecuteReader(string.Format(strSql)))
            {
                return DeepFillData(dr);
            }
        }


        public static List<ArticalEntity> GetArticalList(int pageIndex, int pageSize, int pageCount, int recordCount, int categoryId, int pCategoryId, string innerCode)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            string strSql = string.Empty;
            if (pageIndex >= pageCount)
            {
                if (recordCount == 0)
                {
                    strSql = "select * from (select top " + pageSize + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
                }
                else
                {
                    strSql = "select * from (select top " + (recordCount - (pageIndex - 1) * pageSize) + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
                }
            }
            else
            {
                strSql = "select * from (select top " + pageSize + " * from (select top " + pageIndex * pageSize + " a.*,u.UserName,c.CategoryName from ArticalTB as a,UserTB as u ,CategoryTB as c  where a.CreateUserId=u.UserID and a.CategoryId=c.CategoryId";
            }
            if (categoryId.Equals(0) && pCategoryId.Equals(0))
            {
                strSql += " and a.InnerCode='" + innerCode + "' order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            if (!pCategoryId.Equals(0) && categoryId.Equals(0))
            {
                strSql += " and a.CategoryId in (select CategoryId from CategoryTB where ParentId = " + pCategoryId + " ) order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            if (!categoryId.Equals(0))
            {
                strSql += " and a.CategoryId=" + categoryId + " order by a.ArticalId desc) order by ArticalId) order by ArticalId desc";
            }
            using (var dr = DBHelper.ExecuteReader(string.Format(strSql)))
            {
                return DeepFillData(dr);
            }
        }

        public static List<ArticalEntity> GetArticalList(int tops, int cid)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            string strSql = "select top " + tops + " * from ArticalTB where CategoryId=" + cid + " order by ArticalId desc";
            using (var dr = DBHelper.ExecuteReader(string.Format(strSql)))
            {
                return FillDate(dr);
            }
        }
        public static List<ArticalEntity> GetArticalList(int tops, string innerCode)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            string strSql = "select top " + tops + " * from ArticalTB where InnerCode='" + innerCode + "' order by ArticalId desc";
            using (var dr = DBHelper.ExecuteReader(string.Format(strSql)))
            {
                return FillDate(dr);
            }
        }

        /// <summary>
        /// 根据条件获取信息列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static List<ArticalEntity> GetArticalListByCondition(string condition)
        {
            using (var dr = DBHelper.ExecuteReader(string.Format("SELECT * FROM ArticalTB WHERE  1 = 1 AND '{0}' like '%{0}%'", condition)))
            {
                return FillDate(dr);
            }
        }

        /// <summary>
        /// 获得符合条件的记录数
        /// </summary>
        /// <param name="status"></param>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static int GetArticalListCount(int status, int categoryId,int pCategoryId)
        {
            string strSql = string.Empty;
            if (categoryId.Equals(0) && pCategoryId.Equals(0))
            {
                strSql = "select count(*) as allcounts from ArticalTB where Status=" + status + "";
            }
            if (!pCategoryId.Equals(0) && categoryId.Equals(0))
            {
                strSql = "select count(*) as allcounts from ArticalTB where Status=" + status + " and CategoryId in (select CategoryId from CategoryTB where ParentId = " + pCategoryId + " )";
            }
            if (!categoryId.Equals(0))
            {
                strSql = "select count(*) as allcounts from ArticalTB where Status=" + status + " and CategoryId=" + categoryId + "";
            }
            return DBHelper.ExecuteIntScalar(string.Format(strSql));
        }

        /// <summary>
        /// 获得符合条件的记录数
        /// </summary>
        /// <param name="status"></param>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public static int GetArticalListCount(int status, int categoryId, int pCategoryId,string rootCategory)
        {
            string strSql = string.Empty;
            if (categoryId.Equals(0) && pCategoryId.Equals(0))
            {
                strSql = "select count(*) as allcounts from ArticalTB where Status=" + status + " and InnerCode='" + rootCategory + "'";
            }
            if (!pCategoryId.Equals(0) && categoryId.Equals(0))
            {
                strSql = "select count(*) as allcounts from ArticalTB where Status=" + status + " and CategoryId in (select CategoryId from CategoryTB where ParentId = " + pCategoryId + " )";
            }
            if (!categoryId.Equals(0))
            {
                strSql = "select count(*) as allcounts from ArticalTB where Status=" + status + " and CategoryId=" + categoryId + "";
            }
            return DBHelper.ExecuteIntScalar(string.Format(strSql));
        }

        public static int GetArticalListCount(int categoryId)
        {
            return DBHelper.ExecuteIntScalar(string.Format("select count(*) as allcounts from ArticalTB where CategoryId={0}", categoryId));
        }
        public static int GetArticalListCount(string innerCode)
        {
            return DBHelper.ExecuteIntScalar(string.Format("select count(*) as allcounts from ArticalTB where InnerCode='{0}'", innerCode));
        }

        /// <summary>
        /// 获取信息实体
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static ArticalEntity GetArticalEntity(string condition)
        {
            using (var dr = DBHelper.ExecuteReader(string.Format("SELECT * FROM ArticalTB {0}", condition)))
            {
                List<ArticalEntity> list = FillDate(dr);
                return list == null || list.Count == 0 ? (new ArticalEntity()) : list[0];
            }
        }
        public static ArticalEntity GetArticalEntity(string top,string condition)
        {
            using (var dr = DBHelper.ExecuteReader(string.Format("SELECT {0} * FROM ArticalTB {1}", top, condition)))
            {
                List<ArticalEntity> list = FillDate(dr);
                return list == null || list.Count == 0 ? (new ArticalEntity()) : list[0];
            }
        }
        /// <summary>
        /// 根据标识获取信息实体重载一
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static ArticalEntity GetDeepArticalEntity(int articalId)
        {
            using (var dr = DBHelper.ExecuteReader(string.Format("SELECT a.*,b.UserName,c.CategoryName FROM ArticalTB as a,UserTB as b,CategoryTB as c where a.CreateUserId=b.UserId and a.CategoryId=c.CategoryId and ArticalId = {0}", articalId)))
            {
                List<ArticalEntity> list = DeepFillData(dr);
                return list == null || list.Count == 0 ? (new ArticalEntity()) : list[0];
            }
            //return GetArticalEntity(string.Format("SELECT a.*,b.UserName FROM ArticalTB a LEFT JOIN UserTB ON a.CreateUserId=b.UserId WHERE ArticalId = {0}", articalId));
        }
        public static ArticalEntity GetArticalEntity(int articalId)
        {
            using (var dr = DBHelper.ExecuteReader(string.Format("SELECT * FROM ArticalTB WHERE ArticalId = {0}", articalId)))
            {
                List<ArticalEntity> list = FillDate(dr);
                return list == null || list.Count == 0 ? (new ArticalEntity()) : list[0];
            }
        }
        /// <summary>
        /// 获取该分类下的第一篇文章
        /// </summary>
        /// <param name="cid">分类</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        public static ArticalEntity GetArticalEntity(int cid,int status)
        {
            return GetArticalEntity(string.Format(" WHERE CategoryId = {0} and Status = {1}", cid, status));
        }
        /// <summary>
        /// 检查是否已经存在
        /// </summary>
        /// <param name="teamName"></param>
        /// <returns></returns>
        public static bool CheckArticalIsExisted(string articalTitle)
        {
            using (var dr = DBHelper.ExecuteReader(string.Format("SELECT * FROM ArticalTB WHERE ArticalTitle = '{0}'", articalTitle)))
            {
                return (dr != null && dr.Read());
            }
        }

        /// <summary>
        /// 保存信息
        /// </summary>
        /// <param name="te"></param>
        /// <returns></returns>
        public static bool SaveArticalInfor(ArticalEntity te)
        {
            int i = 0;
            if (te.ArticalId <= 0)
            {
                i = DBHelper.ExecuteNonQuery(string.Format("INSERT INTO ArticalTB (ArticalTitle,CategoryId,ArticalContent,AddDate,UpdateDate,CreateUserId,Status,ArticalImage,ArticalType,ArticalTag,ArticalOriginal,InnerCode,IsBottom) VALUES('{0}',{1},'{2}','{3}','{4}',{5},{6},'{7}',{8},'{9}','{10}','{11}',{12})", te.ArticalTitle, te.CategoryInfo.CategoryId, te.ArticalContent, te.AddDate, te.UpdateDate, te.UserInfo.UserId, te.Status, te.ArticalImage, te.ArticalType, te.ArticalTag, te.ArticalOriginal, te.InnerCode,te.IsBottom));
            }
            else
            {
                i = DBHelper.ExecuteNonQuery(string.Format("UPDATE ArticalTB SET ArticalTitle ='{0}' ,ArticalOriginal ='{1}',ArticalTag='{2}',Status={3},AddDate='{4}',UpdateDate='{5}',ArticalType={6},ArticalHits={7},ArticalContent='{8}',ArticalImage='{10}',InnerCode='{11}',IsBottom={12} WHERE ArticalId = {9}", te.ArticalTitle, te.ArticalOriginal, te.ArticalTag, te.Status, te.AddDate, te.UpdateDate, te.ArticalType, te.ArticalHits, te.ArticalContent, te.ArticalId, te.ArticalImage, te.InnerCode, te.IsBottom));
            }
            return i > 0 ? true : false;
        }

        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteArticalInfor(int articalId)
        {
            return DBHelper.ExecuteNonQuery(string.Format("DELETE FROM ArticalTB WHERE ArticalId = {0}", articalId)) >= 0 ? true : false;
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static List<ArticalEntity> FillDate(IDataReader dr)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            ArticalEntity te;
            while (dr != null && dr.Read())
            {
                te = new ArticalEntity();
                te.ArticalId = ConvertHelper.IntParse(dr["ArticalId"]);
                te.CategoryInfo.CategoryId = ConvertHelper.IntParse(dr["CategoryId"]);
                te.ArticalTitle = ConvertHelper.StringParse(dr["ArticalTitle"]);
                te.ArticalContent = ConvertHelper.StringParse(dr["ArticalContent"]);
                te.AddDate = ConvertHelper.DateTimeParse(dr["AddDate"]);
                te.UpdateDate = ConvertHelper.DateTimeParse(dr["UpdateDate"]);
                te.UserInfo.UserId = ConvertHelper.IntParse(dr["CreateUserId"]);
                te.Status = ConvertHelper.IntParse(dr["Status"]);
                te.ArticalImage = ConvertHelper.StringParse(dr["ArticalImage"]);
                te.ArticalType = ConvertHelper.IntParse(dr["ArticalType"]);
                te.ArticalOriginal = ConvertHelper.StringParse(dr["ArticalOriginal"]);
                te.ArticalHits = ConvertHelper.IntParse(dr["ArticalHits"]);
                te.ArticalTag = ConvertHelper.StringParse(dr["ArticalTag"]);
                te.InnerCode = ConvertHelper.StringParse(dr["InnerCode"]);
                te.IsBottom = ConvertHelper.IntParse(dr["IsBottom"]);

                list.Add(te);
            }
            return list;
        }

        private static List<ArticalEntity> DeepFillData(IDataReader dr)
        {
            List<ArticalEntity> list = new List<ArticalEntity>();
            ArticalEntity te;
            while (dr != null && dr.Read())
            {
                te = new ArticalEntity();
                te.ArticalId = ConvertHelper.IntParse(dr["ArticalId"]);
                te.ArticalTitle = ConvertHelper.StringParse(dr["ArticalTitle"]);
                te.ArticalContent = ConvertHelper.StringParse(dr["ArticalContent"]);
                te.CategoryInfo.CategoryId = ConvertHelper.IntParse(dr["CategoryId"]);
                te.CategoryInfo.CategoryName = ConvertHelper.StringParse(dr["CategoryName"]);
                te.AddDate = ConvertHelper.DateTimeParse(dr["AddDate"]);
                te.UpdateDate = ConvertHelper.DateTimeParse(dr["UpdateDate"]);
                te.UserInfo.UserId = ConvertHelper.IntParse(dr["CreateUserId"]);
                te.UserInfo.UserName = ConvertHelper.StringParse(dr["UserName"]);
                te.Status = ConvertHelper.IntParse(dr["Status"]);
                te.ArticalImage = ConvertHelper.StringParse(dr["ArticalImage"]);
                te.ArticalOriginal = ConvertHelper.StringParse(dr["ArticalOriginal"]);
                te.ArticalHits = ConvertHelper.IntParse(dr["ArticalHits"]);
                te.InnerCode = ConvertHelper.StringParse(dr["InnerCode"]);
                te.ArticalTag = ConvertHelper.StringParse(dr["ArticalTag"]);
                te.ArticalType = ConvertHelper.IntParse(dr["ArticalType"]);
                te.IsBottom = ConvertHelper.IntParse(dr["IsBottom"]);

                list.Add(te);
            }
            return list;
        }
        #endregion
    }
}
